﻿using Dapper;
using DapperExtensions;
using DapperExtensions.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SXFramWork.Core
{
    /// <summary>
    /// Dapper帮助类(说明: 方法名大写采用dapper参数化方式object,小写采用自定义参数化方式dynamic)
    /// </summary>
    public class SqlHelper
    {
        //设置数据库连接信息 读取appsettings.json配置
        private static string ConnStr = ConfigHelper.app(new string[] { "DBConnection", "SqlServer" });
        /// <summary>
        /// 分页总数
        /// </summary>
        public static int TotalCount { get; set; }
        /// <summary>
        /// 初始化连接
        /// </summary>
        /// <returns></returns>
        public static IDbConnection GetConnection()
        {
            var dbType = ConfigHelper.app(new string[] { "DBConnection", "Type" }).To_Int();
            IDbConnection conn = new SqlConnection(ConnStr); 
            return conn;
        }
        /// <summary>
        /// 开启事务
        /// </summary>
        /// <returns></returns>
        public static IDbTransaction BeginTransaction(IsolationLevel isc = IsolationLevel.Serializable)
        {
            IDbConnection conn = GetConnection();
            conn.Open();
            return conn.BeginTransaction(isc);
        }
        /// <summary>
        /// 查询对象集合 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> GetDataListAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryAsync<T>(sql, param);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryAsync<T>(sql, param, transaction);
            }
        }
        /// <summary>
        /// 查询对象集合(In) 
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> GetDataListInAsync<T>(string sql, object obj, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryAsync<T>(sql, obj);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryAsync<T>(sql, obj, transaction);
            }
        }
        /// <summary>
        /// 查询一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static async Task<T> GetDataAsync<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryFirstOrDefaultAsync<T>(sql, param);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryFirstOrDefaultAsync<T>(sql, param, transaction);
            }

        }
        /// <summary>
        /// 查询一个对象 (In)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static async Task<T> GetDataInAsync<T>(string sql, object obj, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryFirstOrDefaultAsync<T>(sql, obj);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryFirstOrDefaultAsync<T>(sql, obj, transaction);
            }
        }
        /// <summary>
        /// 执行增加,修改,删除 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.ExecuteAsync(sql, param);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.ExecuteAsync(sql, param, transaction);
            }
        }

        /// <summary>
        /// 插入，删除，更新 (In) 
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public static async Task<int> ExecuteaInAync(string sql, object obj, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.ExecuteAsync(sql, obj);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.ExecuteAsync(sql, obj, transaction);
            }
        }
        /// <summary>
        /// 执行sql返回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<object> ExecuteScalarAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.ExecuteScalarAsync(sql, param);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.ExecuteScalarAsync(sql, param, transaction);
            }
        }

        /// <summary>
        /// 同时查询多张表数据（高级查询）
        /// "select *from A;select *from B";
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryMultipleAsync(sql, param);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryMultipleAsync(sql, param, transaction);
            }
        }
        /// <summary>
        /// 分页查询(建议使用)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="pageindex">页码</param>
        /// <param name="pagesize">页大小</param>
        /// <param name="fenyezhiduan">分页字段</param>
        /// <param name="paixu">排序</param>
        /// <param name="check">模糊查询</param>
        /// <param name="where">附加where</param>
        /// <param name="param">sql参数</param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> GetPageListAsync<T>(string sql, int pageindex, int pagesize, string fenyezhiduan = "riqi", string paixu = "desc", Dictionary<string, object> check = null, string where = "", object param = null)
        {
            //获得分页总数
            TotalCount = await GetToTalCount(sql, param, check, where);
            /*
             * select top pageSize * 
               from (select row_number() 
               over(order by sno asc) as rownumber,* 
               from student) temp_row
               where rownumber>((pageIndex-1)*pageSize);
           */
            sql = sql.TrimStart();
            List<string> sqllist = new List<string>();
            sqllist.Add("select top " + pagesize + " * from  ( select row_number() over(order by ");
            sqllist.Add(fenyezhiduan + " " + paixu + ") as rownumber, ");
            sql = sql.ToLower();
            sqllist.Add(sql.Substring(sql.IndexOf(" ")));

            //模糊查询     
            if (check != null && check.Count > 0)
            {
                foreach (var key in check.Keys)
                {
                    //筛选名称         
                    where = where + "  and " + key + " like '%" + check[key] + "%'";
                }
            }
            //where 条件
            if (where.Trim().Length > 0)
            {
                sqllist.Add(where);
            }
            sqllist.Add("  ) as temp_tb where rownumber>(" + (pageindex - 1) * pagesize + " )");


            using (IDbConnection conn = GetConnection())
            {
                conn.Open();
                return await conn.QueryAsync<T>(string.Concat(sqllist), param);
            }
        }

        /// <summary>
        /// 获得分页总数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="transaction">事务</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        private static async Task<int> GetToTalCount(string sql, object obj = null, Dictionary<string, object> check = null, string where = "")
        {
            List<string> sqllist = new List<string>();
            sql = "select count(1) from ( " + sql + " )tb where 1=1";
            sqllist.Add(sql);
            //模糊查询     
            if (check != null && check.Count > 0)
            {
                foreach (var key in check.Keys)
                {
                    //筛选名称         
                    where = where + "  and " + key + " like '%" + check[key] + "%'";
                }
            }
            //where 条件
            if (where.Trim().Length > 0)
            {
                sqllist.Add(where);
            }

            object res = null;

            using (var conn = GetConnection())
            {
                conn.Open();
                res = await conn.ExecuteScalarAsync(string.Concat(sqllist), obj);
            }
            return res.To_Int();
        }
        #region 新增方法 参数用@0 @1 @2 @3..

        /// <summary>
        /// 获得分页总数 
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="transaction">事务</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        private static async Task<int> gettotalcount(string sql, Dictionary<string, object> check = null, string where = "", params dynamic[] args)
        {
            List<string> sqllist = new List<string>();
            sql = "select count(1) from ( " + sql + " )tb where 1=1";
            sqllist.Add(sql);
            //模糊查询     
            if (check != null && check.Count > 0)
            {
                foreach (var key in check.Keys)
                {
                    //筛选名称         
                    where = where + "  and " + key + " like '%" + check[key] + "%'";
                }
            }
            //where 条件
            if (where.Trim().Length > 0)
            {
                sqllist.Add(where);
            }
            return await getintasync(string.Concat(sqllist), null, args);
        }
        /// <summary>
        /// 插入，删除，更新   (参数： @0,@1.....)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public static async Task<int> executeasync(string sql, IDbTransaction transaction = null, params dynamic[] args)
        {
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.ExecuteAsync(sql, expando as object);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.ExecuteAsync(sql, expando as object, transaction);
            }
        }
        /// <summary>
        /// 插入，删除，更新   (参数： @0,@1.....)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public static async Task<int> executeasync(string sql, params dynamic[] args)
        {
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            using (var conn = GetConnection())
            {
                conn.Open();
                return await conn.ExecuteAsync(sql, expando as object);
            }
        }


        public static Task<int> excutenotinasync(string sql, string in_where, List<object> in_param, params dynamic[] args)
        {
            Regex reg = new Regex(@"@\d+");
            int in_i = reg.Matches(sql).Count;
            //添加in有多少@
            List<string> sqlarg = new List<string>();
            foreach (var item in in_param)
            {
                sqlarg.Add("@" + in_i);
                in_i++;
            }

            if (!sql.Contains(" where "))
                in_where = " where " + in_where;
            else
                in_where = " and " + in_where;

            sql = sql + " " + in_where + " not in (" + string.Join(",", sqlarg) + ")  ";

            if (!sql.Contains(" where "))//如果没有where就不执行 避免错误执行
                return Task.Run(() => 0);
            var paramlist = new List<object>(args);
            paramlist.AddRange(in_param);
            return SqlHelper.executeasync(sql, paramlist.ToArray());
        }
        /// <summary>
        /// 查询 返回string (sql参数：@0,@1.....)
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static async Task<string> getstrasync(string sql, IDbTransaction transaction = null, params dynamic[] args)
        {
            if (!sql.Contains("top") && Regex.Matches(sql, @"select").Count == 1)
            {
                sql = sql.Replace("select", "select top 1 ");
            }
            object res = null;
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    res = await conn.ExecuteScalarAsync(sql, expando as object);
                }
            }
            else
            {
                var conn = transaction.Connection;
                res = await conn.ExecuteScalarAsync(sql, expando as object, transaction);
            }
            return res.To_String();
        }

        /// <summary>
        /// 查询返回Int (sql参数：@0,@1.....)
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static async Task<int> getintasync(string sql, IDbTransaction transaction = null, params dynamic[] args)
        {
            if (!sql.Contains("top") && Regex.Matches(sql, @"select").Count == 1)
            {
                sql = sql.Replace("select", "select top 1 ");
            }
            object res = null;
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    res = await conn.ExecuteScalarAsync(sql, expando as object);
                }
            }
            else
            {
                var conn = transaction.Connection;
                res = await conn.ExecuteScalarAsync(sql, expando as object, transaction);
            }
            return res.To_Int();
        }

        /// <summary>
        /// 查询集合  (sql参数：@0,@1.....)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> getdatalistasync<T>(string sql, IDbTransaction transaction = null, params dynamic[] args)
        {
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryAsync<T>(sql, expando as object);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryAsync<T>(sql, expando as object, transaction);
            }
        }
        /// <summary>
        /// 查询一个对象 (sql参数：@0,@1.....)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static async Task<T> getdataasync<T>(string sql, IDbTransaction transaction = null, params dynamic[] args)
        {
            var expando = new System.Dynamic.ExpandoObject();
            if (args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    AddProperty(expando, "@" + i, args[i]);
                }
            }
            if (transaction == null)
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return await conn.QueryFirstOrDefaultAsync<T>(sql, expando as object);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.QueryFirstOrDefaultAsync<T>(sql, expando as object, transaction);
            }
        }

       
        #endregion

        #region dapper 批量操作

        /// <summary>
        ///  批量插入
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static async Task<int> AddDataListAsync(string sql, object list, IDbTransaction trans = null)
        {
            if (trans == null)
            {
                using (IDbConnection conn = GetConnection())
                {
                    conn.Open();
                    return await conn.ExecuteAsync(sql, list);
                }
            }
            else
            {
                var conn = trans.Connection;
                return await conn.ExecuteAsync(sql, list, trans);
            }

        }
        /// <summary>
        /// 批量插入 (高效率)
        /// </summary>
        public static async Task addlistasync<T>(IEnumerable<T> entityList, string tablename = "", IDbTransaction transaction = null) where T : class
        {
            var tblName = tablename;
            var tran = (SqlTransaction)transaction;
            IDbConnection Db = null;
            if (transaction == null)
            {

                using (Db = GetConnection())
                {
                    Db.Open();
                    using (var bulkCopy = new SqlBulkCopy(Db as SqlConnection, SqlBulkCopyOptions.TableLock, null))
                    {
                        bulkCopy.BatchSize = entityList.Count();
                        bulkCopy.DestinationTableName = tblName;
                        var table = new DataTable();
                        DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                        var classMap = sqlGenerator.Configuration.GetMap<T>();
                        var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                        foreach (var propertyInfo in props)
                        {
                            bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                            table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                        }
                        var values = new object[props.Count()];
                        foreach (var itemm in entityList)
                        {
                            for (var i = 0; i < values.Length; i++)
                            {
                                values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                            }
                            table.Rows.Add(values);
                        }
                        await bulkCopy.WriteToServerAsync(table);
                    }
                }
            }
            else
            {
                Db = transaction.Connection;
                using (var bulkCopy = new SqlBulkCopy(Db as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
                {
                    bulkCopy.BatchSize = entityList.Count();
                    bulkCopy.DestinationTableName = tblName;
                    var table = new DataTable();
                    DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                    var classMap = sqlGenerator.Configuration.GetMap<T>();
                    var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                    foreach (var propertyInfo in props)
                    {
                        bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                        table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                    }
                    var values = new object[props.Count()];
                    foreach (var itemm in entityList)
                    {
                        for (var i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                        }
                        table.Rows.Add(values);
                    }
                    await bulkCopy.WriteToServerAsync(table);
                }
            }

        }
        #endregion
        /// <summary>
        /// 匿名对象添加属性
        /// </summary>
        /// <param name="expando"></param>
        /// <param name="propertyName"></param>
        /// <param name="propertyValue"></param>
        public static void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
        {
            //扩展对象支持IDictionary，因此我们可以像这样扩展它
            var expandoDict = expando as IDictionary<string, object>;
            if (expandoDict.ContainsKey(propertyName)) //是否包含该属性
                expandoDict[propertyName] = propertyValue;
            else
                expandoDict.Add(propertyName, propertyValue);
        }
    }
}
